<?php
class QueryComponent extends Component
{


function app($model=null, $array=null,$sql=null)
{
	$data=array(); 
	App::import('Model', $model);
	for($i=0; $i<count($array); $i++)
	{
		$obj = new Sigesp(false,null,null,'sigesp',$array[$i],1);
		$nuevo[$i]=$obj->query($sql);
		for($z=0; $z<count($nuevo[$i]); $z++)
		{
			$data[]=$nuevo[$i][$z][0];
		}
			//     pr($data);
	}
	return $data;
}

function app_tienda($model=null, $array=null,$sql=null)
{
	$data=array(); $datos=array(); 
	App::import('Model', $model);
	for($i=0; $i<count($array); $i++)
	{
		$obj = new Sigesp(false,null,null,'sigesp',$array[$i],1);
		$nuevo[$i]=$obj->query($sql);
		$data=null;
		for($z=0; $z<count($nuevo[$i]); $z++)
		{
			$data[]=$nuevo[$i][$z][0];
		}
		$datos[$array[$i]]=$data;
			//     pr($data);
	}
	return $datos;
}
/*
 nomarch character varying(250) NOT NULL,
  fecarch timestamp without time zone NOT NULL,
  staarch character(1) NOT NULL DEFAULT 'L'::bpchar,
  objarchxls oid,
  objarchsh oid,
  fecinicio timestamp without time zone NOT NULL,
  fecfin timestamp without time zone,
  tiendas character varying(250),
  tiplista
*/
function lista_replica_precios($nomarch=null,$staarch=null,$fecinicio=null,$tiendas=null,$tiplista=null,$id_arch=null)
{
	$sql="SELECT a.*, (select count(distinct(b.codart)) from pre_precio_articulos b where b.idarch=a.id) as articulos FROM pre_archivo_precios a where 1=1";
	if(isset($id_arch))
	{
		$sql.=" and a.id=$id_arch";
	}
	if(isset($nomarch))
	{
		$sql.=" and a.nomarch ilike '%$nomarch%'";
	}
	if(isset($staarch))
	{
		$sql.=" and a.staarch ='$staarch'";
	}
	if(isset($fecinicio))
	{
		$sql.=" and a.fecinicio::date ='$fecinicio'";
	}
	if(isset($tiendas))
	{
		$sql.=" and a.tiendas ='$tiendas'";
	}
	if(isset($tiplista))
	{
		$sql.=" and a.tiplista ='$tiplista'";
	}
	$sql.=" order by a.fecinicio desc, a.id asc";
	return $sql;
}

function lista_replica_precios_detalles($id_arch=null)
{
	$sql="SELECT a.* FROM pre_precio_articulos a WHERE  1=1";
	if(isset($id_arch))
	{
		$sql.=" and a.idarch=$id_arch";
	}
	$sql.=" order by a.id asc";
	return $sql;
}

function cierres_detallado($fecha_a=null, $fecha_b=null)
{
if($fecha_a==null)$fecha_a=date('Y-m-d');
if($fecha_b==null)$fecha_b=date('Y-m-d');

$sql="select (select codtiend || ' - '|| dentie from sfc_tienda) as agencia,
	ar.cod_arqueo,
       ar.cod_caja,
       trim(ar.codusu) as codusu,
       ar.fecarq as fecha_arqueo,
       (select COALESCE(sum(dtar.monto),0) from sfc_dt_arqueocaja dtar where ar.cod_arqueo=dtar.cod_arqueo ) as monto_total,
       (select COALESCE(sum(dtar.monto),0) from sfc_dt_arqueocaja dtar where ar.cod_arqueo=dtar.cod_arqueo AND  dtar.codforpag ='01' ) as efectivo,
       (select COALESCE(sum(dtar.monto),0) from sfc_dt_arqueocaja dtar where ar.cod_arqueo=dtar.cod_arqueo AND  dtar.codforpag ='02' ) as cheque,
       (select COALESCE(sum(dtar.monto),0) from sfc_dt_arqueocaja dtar where ar.cod_arqueo=dtar.cod_arqueo AND  dtar.codforpag ='05' ) as deposito,
       (select COALESCE(sum(dtar.monto),0) from sfc_dt_arqueocaja dtar where ar.cod_arqueo=dtar.cod_arqueo AND  dtar.codforpag ='10' ) as TDD,
       (select COALESCE(sum(dtar.monto),0) from sfc_dt_arqueocaja dtar where ar.cod_arqueo=dtar.cod_arqueo AND  dtar.codforpag ='11' ) as TDC,
       (select COALESCE(sum(dtar.monto),0) from sfc_dt_arqueocaja dtar where ar.cod_arqueo=dtar.cod_arqueo AND  dtar.codforpag ='12' ) as TDDE,
       (select COALESCE(sum(round(cast(f.monto as numeric),2)),0) from sfc_factura f where f.codciecaj =c.codciecaj and f.estfaccon <> 'A' and conpag in (2,4) and f.fecemi::date=c.feccie::date) as Total_CXC,
       (select COALESCE(sum(round(cast(i.montoret as numeric),2)),0) from sfc_instpago i,sfc_factura f where f.codciecaj =c.codciecaj and f.estfaccon <> 'A' and f.numfac=i.numfac and codban='004' ) as Total_Ret_BDV,
       (select COALESCE(sum(round(cast(i.montoret_islr as numeric),2)),0) from sfc_instpago i,sfc_factura f where f.codciecaj =c.codciecaj and f.estfaccon <> 'A' and f.numfac=i.numfac and codban='004' ) as Total_Ret_ISLR_BDV,
       (select COALESCE(sum(round(cast(i.montoret as numeric),2)),0) from sfc_instpago i,sfc_factura f where f.codciecaj =c.codciecaj and f.estfaccon <> 'A' and f.numfac=i.numfac and codban='205' ) as Total_Ret_BAV,
       (select COALESCE(sum(round(cast(i.montoret_islr as numeric),2)),0) from sfc_instpago i,sfc_factura f where f.codciecaj =c.codciecaj and f.estfaccon <> 'A' and f.numfac=i.numfac and codban='205' ) as Total_Ret_ISLR_BAV,
       (select COALESCE(sum(round(cast(i.montoret as numeric),2)),0) from sfc_instpago i,sfc_factura f where f.codciecaj =c.codciecaj and f.estfaccon <> 'A' and f.numfac=i.numfac and codban='252' ) as Total_Ret_BB,
       (select COALESCE(sum(round(cast(i.montoret_islr as numeric),2)),0) from sfc_instpago i,sfc_factura f where f.codciecaj =c.codciecaj and f.estfaccon <> 'A' and f.numfac=i.numfac and codban='252' ) as Total_Ret_ISLR_BB,
       (select COALESCE(sum(round(cast(i.montoret as numeric),2)),0) from sfc_instpago i,sfc_factura f where f.codciecaj =c.codciecaj and f.estfaccon <> 'A' and f.numfac=i.numfac) as Total_Comisiones_Bancarias,       
       (select COALESCE(sum(round(cast(i.montoret as numeric),2)),0) from sfc_instpago i,sfc_factura f where f.codciecaj =c.codciecaj and f.estfaccon <> 'A' and f.numfac=i.numfac and i.codforpag='11') as Total_Comisiones_TDC,
       (select COALESCE(sum(round(cast(i.montoret_islr as numeric),2)),0) from sfc_instpago i,sfc_factura f where f.codciecaj =c.codciecaj and f.estfaccon <> 'A' and f.numfac=i.numfac) as Total_Ret_ISLR
from	sfc_arqueocaja ar,
	sfc_cierrecaja c
where	ar.status='R' 
	and c.cod_arqueo=ar.cod_arqueo
	and ar.fecarq::date between '$fecha_a' and '$fecha_b';";


return $sql;
}


function movimiento_de_materiales(){
$sql="  select  fecmov::date,
        cenrec,
        stareg,
        count(cenrec) as cant_mov,
        (select count (cenrec)
        from int_archivos_movimientos mov1
        where /*fecmov::date=now()::date and*/ stareg='N' and canmov>0 and mov1.cenrec=mov.cenrec ) as entrada,

        (select count (cenrec)
        from int_archivos_movimientos  mov2
        where /*fecmov::date=now()::date and*/ stareg='N' and canmov<0 and mov2.cenrec=mov.cenrec) as despacho

from         int_archivos_movimientos mov
where   stareg='N'
        --and fecmov::date=now()::date
group by 1,2,3
order by 2
";


return $sql;
}

function movimiento_de_materiales_individual($tienda=null,$fecha=null){
$sql="  select  * from int_archivos_movimientos
where fecmov::date='".$fecha."'
--and stareg!='L'
and cenrec='".$tienda."'
order by stareg DESC
";


return $sql;
}
function select_movimiento_de_materiales_individual_estatus($nrodoc=null, $codart=null,$tienda=null, $fecha=null){

$sql="  select  * from int_archivos_movimientos
where fecmov::date='".$fecha."'
--and stareg!='L'
and cenrec='".$tienda."'
and nrodoc='".$nrodoc."'
and codart='".$codart."'
order by stareg DESC
";
return $sql;
}
function update_movimiento_de_materiales_individual_estatus($nrodoc=null, $codart=null,$tienda=null, $fecha=null,$stareg=null){

$sql="  update int_archivos_movimientos SET stareg='$stareg'
where fecmov::date='".$fecha."'
--and stareg!='L'
and cenrec='".$tienda."'
and nrodoc='".$nrodoc."'
and codart='".$codart."'
";
return $sql;
}





function correlativos_caja(){
$sql="SELECT * FROM sfc_secuencias where 1=1;";

return $sql;
}




function facturas_no_procesadas($fact=null,$tien=null){
				$fact = str_replace(' ','',trim($fact));
				$tien = str_replace(' ','',strtoupper(trim($tien)));
$sql="select a.*, b.* from sfc_factura a, sfc_cliente b where a.codcli=b.codcli and a.numfac like '%$fact%' and codtiend='$tien'";

return $sql;
}


function reporte_de_ventas($fecha_a=null, $fecha_b=null,$fpago=null,$material=null,$nfactura=null,$mfactura=null,$blanco=0,$numinst=0)
{
	if($fecha_a==null)$fecha_a=date('Y-m-d');
	if($fecha_b==null)$fecha_b=date('Y-m-d');
	$sql="SELECT  (LPAD( ( substring(fac.numfac,5,3) || substring(fac.numfac,19) ) ,10,'0')) as nro_documento,
			cli.razcli as nombre_cliente,
			cli.cedcli as rif,
			cli.staclisap,
			cli.codclisap,
			cli.codpai,
			cli.codest,
			cli.codmun,
			cli.codpar,
			cli.dircli,
			fac.numcot,
			(select codlapso from sfc_cotizacion cot where fac.numcot=cot.numcot) as condicion_pago,
			(select codpro from sfc_cotizacion cot where fac.numcot=cot.numcot) as cod_programa,
			fac.fecemi,
			fac.conpag,
			fac.estfaccon,
			fac.monto,
			fac.codtiend,
			(substring(fac.numcon,14)) as nro_control,
			substring(fac.numcarta,16) as numcarta,
			(LPAD( ( substring(nt.numnot,strpos(nt.numnot,'-')+1,3) || substring(nt.numnot from '.......$') ) ,10,'0')) as nota,
			nt.estnota,
			nt.tipnot,
			nt.monto as monto_nota,
			dtf.codart,
			dtf.canpro,
			dtf.prepro,
			ip.codforpag as codforpag_instpago,
			ip.numinst as numinst_instpago,
			ip.codban as codban_instpago,
			ip.monto as monto_instpago,
			ip.porreten as porreten_instpago,
			ip.montoret as montoret_instpago,
			ip.montoret_islr as monto_islr,
			(select pto.tiptarsap  from sfc_ptoventa pto where ip.codforpag=pto.codforpag and ip.codban=pto.codban) as tiptarsap,
			(select pto.tipcomsap  from sfc_ptoventa pto where ip.codforpag=pto.codforpag and ip.codban=pto.codban) as tipcomsap,
			(select pto.tipisrlsap  from sfc_ptoventa pto where ip.codforpag=pto.codforpag and ip.codban=pto.codban) as tipisrlsap,
			car.id_entidad as id_entidad, 
			cre.denominacion as den_entidad
		FROM    sfc_factura fac 
			left join sfc_instpago ip on fac.numfac=ip.numfac 
			left join sfc_nota nt on fac.numfac=nt.nro_documento 
			left join sfc_carta_orden car on fac.numcarta=car.numcarta 
			left join sfc_entidadcrediticia cre on car.id_entidad=cre.id_entidad AND cre.id_entidad>0,  
			sfc_cliente cli, 
			sfc_detfactura dtf	
		WHERE 	fac.fecemi::date between '$fecha_a' and '$fecha_b'
			AND fac.codcli=cli.codcli 
			AND fac.numfac=dtf.numfac ";
		if($fpago!=null and $fpago!='')	$sql.=" AND ip.codforpag='$fpago' ";
		if($material!=null and $material!='') $sql.=" AND dtf.codart like '%$material%' "; 
		if($nfactura!=null and $nfactura!='') $sql.=" AND fac.numfac like '%$nfactura%' "; 
		if($mfactura!=null and $mfactura!='') $sql.=" AND CAST(dtf.prepro AS VARCHAR(30)) like '%$mfactura%' ";
		if($blanco && !$numinst)	$sql.=" AND ip.codforpag in ('10','11','12') AND ip.porreten=0";
		elseif($numinst && !$blanco) $sql.=" AND ip.codforpag in ('10','11','12') AND length(ip.numinst) > 10";
		else if ($blanco && $numinst) $sql.=" AND ((ip.codforpag in ('10','11','12') AND ip.porreten=0) OR (ip.codforpag in ('10','11','12') AND length(ip.numinst) > 10))";
		$sql.=" ORDER BY nro_documento;";
		return $sql;
}


function movimientos_duplicados($fecha_a=null, $fecha_b=null){
if($fecha_a==null)$fecha_a=date('Y-m-d');
if($fecha_b==null)$fecha_b=date('Y-m-d');


$sql="select count(s.*) as cantidad,s.codart, s.numdoc, s.canart, s.codtiend, convert(sa.denart, 'LATIN9', 'UTF8') as denart, s.fecmov from siv_dt_movimiento s, siv_articulo sa WHERE s.codart=sa.codart AND s.fecmov::date between '$fecha_a' and '$fecha_b' group by s.codart, s.numdoc, s.canart, s.codtiend, sa.denart, s.fecmov having count(*)>1;";


return $sql;
}

function busqueda_cheques($fecha_a=null, $fecha_b=null,$monto=null,$fpago=null,$cheques=null)
{
	$sql1="SELECT a.numfac as factura, a.numinst as numinst, a.monto as monto, a.codtiend as tienda, b.fecemi::date as fecha, convert(c.nomban, 'LATIN9', 'UTF8') as banco, d.denforpag as forma_pago, e.cedcli as rif, convert(e.razcli, 'LATIN9', 'UTF8') as razcli, e.telcli as tel FROM sfc_instpago a, sfc_factura b, scb_banco c, sfc_formapago d, sfc_cliente e WHERE a.numfac=b.numfac AND a.codcli=e.codcli AND a.codban=c.codban AND a.codforpag=d.codforpag";
	$sql2="SELECT f.numfac as factura, a.numinst as numinst, a.monto as monto, a.codtiend as tienda, b.fecemi::date as fecha, convert(c.nomban, 'LATIN9', 'UTF8') as banco, d.denforpag as forma_pago, e.cedcli as rif, convert(e.razcli, 'LATIN9', 'UTF8') as razcli, e.telcli as tel FROM sfc_instpagocob a, sfc_dt_cobrocliente f, sfc_factura b, scb_banco c, sfc_formapago d, sfc_cliente e WHERE a.numcob=f.numcob AND a.monto=f.moncancel AND f.numfac=b.numfac AND a.codcli=e.codcli AND a.codban=c.codban AND a.codforpag=d.codforpag";
	$sql3="SELECT f.numfac as factura, a.numinst as numinst, f.moncancel as monto, a.codtiend as tienda, b.fecemi::date as fecha, convert(c.nomban, 'LATIN9', 'UTF8') as banco, d.denforpag as forma_pago, e.cedcli as rif, convert(e.razcli, 'LATIN9', 'UTF8') as razcli, e.telcli as tel FROM sfc_instpagocob a, sfc_dt_cobrocliente f, sfc_factura b, scb_banco c, sfc_formapago d, sfc_cliente e WHERE a.numcob=f.numcob AND a.monto=(SELECT sum(dt1.moncancel) as moncancel FROM sfc_dt_cobrocliente dt1 WHERE dt1.numcob=a.numcob GROUP BY dt1.numcob HAVING COUNT(dt1.numcob)>1 ) AND f.numfac=b.numfac AND a.codcli=e.codcli AND a.codban=c.codban AND a.codforpag=d.codforpag";

	if(isset($fecha_a) or isset($fecha_b))
	{
		if(isset($fecha_a) and !isset($fecha_b))
		{
			$fecha_b=$fecha_a;
		}
		else if(isset($fecha_b) and !isset($fecha_a))
		{
			$fecha_a=$fecha_b;
		}
		$sql1.=" and b.fecemi::date between '$fecha_a' and '$fecha_b'";
		$sql2.=" and b.fecemi::date between '$fecha_a' and '$fecha_b'";
		$sql3.=" and b.fecemi::date between '$fecha_a' and '$fecha_b'";
	}
	if(isset($fpago) and strlen($fpago)>0)
	{
		$sql1.=" and a.codforpag= '$fpago'";
		$sql2.=" and a.codforpag= '$fpago'";
		$sql3.=" and a.codforpag= '$fpago'";
	}
	if(isset($monto) and $monto >0)
	{
		$sql1.=" and a.monto= $monto";
		$sql2.=" and a.monto= $monto";
		$sql3.=" and f.moncancel= $monto";
	}
	if(isset($cheques))
	{
		$sql1.=" and ($cheques)";
		$sql2.=" and ($cheques)";
		$sql3.=" and ($cheques)";
	}
	$sql= trim($sql1)."UNION ".trim($sql2)." UNION ".trim($sql3);
	return trim($sql);
}


function apertura_cierre_mes($mes=null){

		 if($mes==1)$msx='m01';
else if($mes==2)$msx='m02';
else if($mes==3)$msx='m03';
else if($mes==4)$msx='m04';
else if($mes==5)$msx='m05';
else if($mes==6)$msx='m06';
else if($mes==7)$msx='m07';
else if($mes==8)$msx='m08';
else if($mes==9)$msx='m09';
else if($mes==10)$msx='m10';
else if($mes==11)$msx='m11';
else if($mes==12)$msx='m12';
else $msx=null;



if($msx!=null){

$sql="SELECT $msx AS mes FROM sigesp_empresa";
}else{

$sql="SELECT m01,m02,m03,m04,m05,m06,m07,m08,m09,m10,m11,m12, (SELECT codtiend FROM sfc_secuencias) AS codtiend FROM sigesp_empresa";
}

return $sql;
}
function update_apertura_cierre_mes($mes=null,$valor=null){

		 if($mes==1)$msx='m01';
else if($mes==2)$msx='m02';
else if($mes==3)$msx='m03';
else if($mes==4)$msx='m04';
else if($mes==5)$msx='m05';
else if($mes==6)$msx='m06';
else if($mes==7)$msx='m07';
else if($mes==8)$msx='m08';
else if($mes==9)$msx='m09';
else if($mes==10)$msx='m10';
else if($mes==11)$msx='m11';
else if($mes==12)$msx='m12';


if($valor=='si')$valr=0;else$valr=1;

if($mes!=null){
$sql="UPDATE sigesp_empresa SET $msx=$valr";
}
//$sql="SELECT m01,m02,m03,m04,m05,m06,m07,m08,m09,m10,m11,m12, (SELECT codtiend FROM sfc_secuencias) AS codtiend FROM sigesp_empresa";

return $sql;
}


	function datos_tiendas_integrador()
	{
$sql="SELECT S.CODTIEND,S.CODEST,E.DESEST,R.DESREG,S.DENTIE FROM INTERFACES.INT_SERVIDORES_AGENCIAS S, REPORTES.SIGESP_REGIONES R, REPORTES.SIGESP_ESTADOS E
                   WHERE S.CODEST=E.CODEST AND S.CODREG=R.CODREG";

return $sql;

	}


	function busca_inventario()
	{
		$sql=" select 
t.codtiend as codtiend,
convert(t.dentie, 'LATIN9', 'UTF8') as agencia, 
aa.codart as codigo_articulo, 
convert(ar.denart, 'LATIN9', 'UTF8') as descripcion, 
convert(cl.dencla, 'LATIN9', 'UTF8') as clasificacion, 
convert(su.den_sub, 'LATIN9', 'UTF8') as sub_clasificacion, 
p.cosproart as costo_promedio_uni, 
sum(aa.existencia) as existencia,
(select estemp from sigesp_empresa limit 1) as estado,
(select CODTIEND from SFC_TIENDA T limit 1) as CODIGO_AGENCIA,
(select DENTIE from SFC_TIENDA T limit 1) as NOMBRE_AGENCIA,


(select lp.precio from sfc_detlistaprecio lp where lp.codart=aa.codart and lp.codlapso='Z001' and lp.codpro='0001' and lp.codlistpre='AGRI') as precio_contado_agri, 
round((sum(aa.existencia) * p.cosproart)::decimal,2) as costo_promedio, 
round((sum(aa.existencia) * (select lp.precio from sfc_detlistaprecio lp where lp.codart=aa.codart and lp.codlapso='Z001' and lp.codpro='0001' and lp.codlistpre='AGRI'))::decimal,2) as precio_promedio, 
coalesce((select sum(df.canpro) from sfc_detfactura df, 
sfc_factura f where f.numfac=df.numfac and f.estfaccon<>'A' and aa.codart=df.codart and f.fecemi::date=now()::date),0) as cantidad_vendida_hoy from sfc_tienda t, siv_articuloalmacen aa, siv_articulo ar, sfc_producto p, sfc_clasificacion cl, sfc_subclasificacion su where t.codtiend=aa.codtiend and aa.codart=ar.codart and p.codart=aa.codart and cl.codcla=ar.codcla and (su.codcla=ar.codcla and su.cod_sub=ar.cod_sub) and aa.existencia >0 group by 1,2,3,4,5,6,7 order by 5,6,8";
		return $sql;
	}


function cambiar_correlativo_factura_select(/*$tipo=null,*/$codtiend=null, $numfac=null, $numcon=null, $codusu=null, $nuevo=null, $monto=null,$fecemi=null){
if(isset($codtiend) and isset($numfac) and isset($numcon) and isset($codusu) and isset($nuevo) and isset($monto) and isset($fecemi)){
$sql="select codtiend,numfac,monto,numcon,codusu,fecemi from sfc_factura as a where 
codtiend='".trim($codtiend)."'
 AND numfac='".trim($numfac)."'
 AND monto='".trim($monto)."'";
/*
if($tipo==2){
$sql.="
 AND numcon='".trim($nuevo'";
}else{*/
$sql.="
 AND numcon='".trim($numcon)."'";
//}
$sql.="
 AND codusu='".trim($codusu)."'
 AND fecemi='".trim($fecemi)."'

";
}
else{
$sql="";
}

return $sql;
}
function cambiar_correlativo_factura_update(/*$tipo=null,*/$codtiend=null, $numfac=null, $numcon=null, $codusu=null, $nuevo=null, $monto=null,$fecemi=null){
if(isset($codtiend) and isset($numfac) and isset($numcon) and isset($codusu) and isset($nuevo) and isset($monto) and isset($fecemi)){
$sql="update sfc_factura SET 
";
/*
if($tipo==2){
$sql.="
numcon='$numcon'";
}else{*/
$sql.="
numcon='$nuevo'";
//}

$sql.="
WHERE 
codtiend='$codtiend'
 AND numfac='$numfac'
 AND monto='$monto'
 AND numcon='$numcon'
 AND codusu='$codusu'
 AND fecemi='$fecemi'

";
}
else{
$sql="";
}

return $sql;
}




}
